﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.Sql;

/// <summary>
/// Summary description for ExhibitionDAO
/// </summary>
public class ExhibitionDAO : DBAccess, IExhibition
{
    #region sql commands
    private string SQL_SELECT_ALL = "select * from Exhibition";
    private string SQL_SELECT_EXHIBITION_BY_ID = "select * from Exhibition where ExhibitionID = @exhibitionId";
    private string SQL_SELECT_EXHIBITION_NAME = "select * from Exhibition where ExhibitionName like (@exhibitionName)";
    private string SQL_INSERT_EXHIBITION = "insert into Exhibition(ExhibitionName, Topic, DateStart, DateEnd, Place, Organizer) values(@exhibitionName, @topic, @dateStart, @dateEnd, @place, @organizer)";
    private string SQL_UPDATE_EXHIBITION = "update Exhibition set ExhibitionName = @exhibitionName, Topic = @topic, DateStart = @dateStart, DateEnd = @dateEnd, Place = @place, Organizer = @organizer where ExhibitionID = @exhibitionId";
    private string SQL_DELETE_EXHIBITION = "delete from Exhibition where ExhibitionID = @exhibitionId";
    private string SQL_SELECT_UPCOMING = "select * from Exhibition where DateStart > Getdate()";
    private string SQL_SELECT_ONGOING = "select * from Exhibition where DateStart < Getdate() and DateEnd > Getdate()";
    private string SQL_SELECT_COMPLETED = "select * from Exhibition where DateEnd < Getdate()";
    

    string message;
    #endregion

    public ExhibitionDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    #region IExhibition Members

    /// <summary>
    /// Get all exhibitions
    /// </summary>
    /// <returns>IList</returns>
    public IList<ExhibitionInfo> GetAll()
    {
        IList<ExhibitionInfo> exhList = new List<ExhibitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExhibitionInfo exh = new ExhibitionInfo();
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);

                    exhList.Add(exh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exhList;
    }

    /// <summary>
    /// Search exhibition by name
    /// </summary>
    /// <param name="name"></param>
    /// <returns>IList</returns>
    public IList<ExhibitionInfo> GetByName(string name)
    {
        IList<ExhibitionInfo> exhList = new List<ExhibitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_EXHIBITION_NAME, conn);
             cmd.Parameters.AddWithValue("@exhibitionName", name);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExhibitionInfo exh = new ExhibitionInfo();
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);

                    exhList.Add(exh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exhList;           
    }

    /// <summary>
    /// Insert exhibition
    /// </summary>
    /// <param name="exh"></param>
    /// <returns>int</returns>
    public int InsertExhibition(ExhibitionInfo exh)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT_EXHIBITION, conn);
            cmd.Parameters.AddWithValue("@exhibitionName", exh.ExhibitionName);
            cmd.Parameters.AddWithValue("@topic", exh.Topic);
            cmd.Parameters.AddWithValue("@dateStart", exh.DateStart);
            cmd.Parameters.AddWithValue("@dateEnd", exh.DateEnd);
            cmd.Parameters.AddWithValue("@place", exh.Place);
            cmd.Parameters.AddWithValue("@organizer", exh.Organizer);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Update exhibition
    /// </summary>
    /// <param name="exh"></param>
    /// <returns>int</returns>
    public int UpdateExhibition(ExhibitionInfo exh)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_EXHIBITION, conn);
            cmd.Parameters.AddWithValue("@exhibitionName", exh.ExhibitionName);
            cmd.Parameters.AddWithValue("@topic", exh.Topic);
            cmd.Parameters.AddWithValue("@dateStart", exh.DateStart);
            cmd.Parameters.AddWithValue("@dateEnd", exh.DateEnd);
            cmd.Parameters.AddWithValue("@place", exh.Place);
            cmd.Parameters.AddWithValue("@organizer", exh.Organizer);
            cmd.Parameters.AddWithValue("@exhibitionId", exh.ExhibitionID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Delete exhibition
    /// </summary>
    /// <param name="id"></param>
    /// <returns>int</returns>
    public int DeleteExhibition(int id)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_EXHIBITION, conn);
            cmd.Parameters.AddWithValue("@exhibitionId", id);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Get all upcoming exhibition
    /// </summary>
    /// <returns>Ilist</returns>
    public IList<ExhibitionInfo> GetUpcoming()
    {
        IList<ExhibitionInfo> exhList = new List<ExhibitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_UPCOMING, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExhibitionInfo exh = new ExhibitionInfo();
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);

                    exhList.Add(exh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exhList;
    }

    /// <summary>
    /// Get Exhibition by ExhibitionID
    /// </summary>
    /// <returns>ExhibitionInfo</returns>
    public ExhibitionInfo GetbyID(int exhibitionID)
    {
        ExhibitionInfo exh = new ExhibitionInfo();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_EXHIBITION_BY_ID, conn);
            cmd.Parameters.AddWithValue("@exhibitionId", exhibitionID);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {   
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);
                    break;
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exh;
    }



    /// <summary>
    /// Get Ongoing ExhibitionInfo
    /// </summary>
    /// <returns>IList</returns>
    public IList<ExhibitionInfo> GetOngoing()
    {
        IList<ExhibitionInfo> exhList = new List<ExhibitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ONGOING, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExhibitionInfo exh = new ExhibitionInfo();
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);

                    exhList.Add(exh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exhList;
    }

    /// <summary>
    /// Get completed Exhibition
    /// </summary>
    /// <returns>IList</returns>
    public IList<ExhibitionInfo> GetComplete()
    {
        IList<ExhibitionInfo> exhList = new List<ExhibitionInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_COMPLETED, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExhibitionInfo exh = new ExhibitionInfo();
                    exh.ExhibitionID = reader.GetInt32(0);
                    exh.ExhibitionName = reader.GetString(1);
                    exh.Topic = reader.GetString(2);
                    exh.DateStart = reader.GetDateTime(3);
                    exh.DateEnd = reader.GetDateTime(4);
                    exh.Place = reader.GetString(5);
                    exh.Organizer = reader.GetString(6);

                    exhList.Add(exh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return exhList;
    }
    #endregion



}